package com.huitian.net.utils

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet, SQLException, Statement}

import com.google.inject.Inject
import com.huitian.net.MysqlConfiguration
import javax.inject.Singleton

import scala.collection.mutable.ArrayBuffer

/**
 * MySQL 工具
 * @param mysqlConf MySQL 配置
 */
@Singleton
class JdbcUtil @Inject()(mysqlConf: MysqlConfiguration) extends Serializable {

  /**
   * 获取数据库连接
   * @return MysqlConnection 返回数据库连接
   */
  def getConn: Connection = {
    val mysqlConf        = new MysqlConfiguration
    val url: String      = mysqlConf.mysqlJdbcUrl
    val driver: String   = mysqlConf.mysqlJdbcDriver
    val user: String     = mysqlConf.dataSourceUser
    val pwd: String      = mysqlConf.dataSourcePassword

    Class.forName(driver)
    try {
      DriverManager.getConnection(url, user, pwd) // 返回数据库连接
    } catch {
      case ex: Exception => {
        println("获取数据库连接错误：Exception=" + ex)
      }
        null;
    }
  }

  def close(ps: PreparedStatement, conn: Connection): Unit = {
    try {
      if (ps != null)
        ps.close()
      if (conn != null)
        conn.close()
    } catch {
      case e: SQLException =>
        println(e.printStackTrace())
    }
  }


  /**
   * 批量更新
   * @param sqlText sql 文本语句
   * @param params 参数列表数组
   */
  def doBatch(sqlText: String, params: ArrayBuffer[Array[Any]]): Unit = {
    val conn  = getConn
    val pstat = conn.prepareStatement(sqlText)// 预编译 sql 语句
    try {
      conn.setAutoCommit(false)
      if (params.nonEmpty) { // 判断sql 数据数组是否为空
        pstat.clearBatch() // 清除批处理
        for (param <- params) {
          for (index <- param.indices) {
            pstat.setObject(index + 1, param(index)) // index + 1 是指 sql 语句的第几个参数, param(index)是要设置的值
          }
          pstat.addBatch() // 添加到批次中
        }
        pstat.executeBatch() // 批量执行
        conn.commit()
        params.clear() // 清除 params 数组
      }
    } catch {
      case e: SQLException =>
        println(s"插入mysqls异常,状态码：${e.getSQLState}，错误码：${e.getErrorCode},message:${e.getMessage}")
        println(e.printStackTrace())
        try {
          println("插入异常，sql回滚")
          conn.rollback()
        } catch {
          case er: SQLException =>
            println(er.printStackTrace())
        }
    } finally {
      close(pstat, conn)
    }
  }


  /**
   * 读取 MySQL 配置为广播变量
   * @return  Map[String, Any]
   */
  def getBroadcast: Map[String, String] = {

    var map              = Map[String,String]()
    var conn: Connection = null
    var stmt: Statement  = null
    var rs: ResultSet    = null
    val mysqlConfig      = new MysqlConfiguration
    val sqlText          = "select * from geohash"

    try {
      conn = getConn
      stmt = conn.createStatement
      rs   = stmt.executeQuery(sqlText)
      while (rs.next) {
        val hashcode = rs.getString("hashcode") // ttxgp
        val region   = rs.getString("region")   // 广东省_广州市_番禺区
        map += hashcode -> region
      }
    } catch {
      case e: Exception => e.printStackTrace()
        println("Mysql连接失败:" + e)
    } finally {
      conn.close()
    }
    map
  }

  def getCaroil: Map[String, String] = {

    var map              = Map[String,String]()
    var conn: Connection = null
    var stmt: Statement  = null
    var rs: ResultSet    = null
    val mysqlConfig      = new MysqlConfiguration
    val sqlText          = "select * from oil_capacity"

    try {
      conn = getConn
      stmt = conn.createStatement
      rs   = stmt.executeQuery(sqlText)
      while (rs.next) {
        val code = rs.getString("code") //
        val capacity   = rs.getString("capacity")   //
        map += code -> capacity
      }
    } catch {
      case e: Exception => e.printStackTrace()
        println("Mysql连接失败:" + e)
    } finally {
      conn.close()
    }
    map
  }
}
